package com.prince.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import static java.lang.System.out;

public class PoiExcelExportUtil {
    private static final org.slf4j.Logger logger = LoggerFactory.getLogger(PoiExcelExportUtil.class);

    //表头背景色
    private String titleBackColor = "00FFFF";
    //Float类型数据小数位
    private String floatDecimal = ".00";
    //Double类型数据小数位
    private String doubleDecimal = ".00";
    //设置列的公式
    private String colFormula[] = null;

    Sheet sheet = null;

    DecimalFormat floatDecimalFormat = new DecimalFormat(floatDecimal);
    DecimalFormat doubleDecimalFormat = new DecimalFormat(doubleDecimal);
    private SXSSFWorkbook workbook = null;

    public PoiExcelExportUtil() {
        workbook = new SXSSFWorkbook();
    }

    //设置表头背景色
    private void setTitleBackColor(String titleBackColor) {
        this.titleBackColor = titleBackColor;
    }

    //设置Double类型格式
    private void setDoubleDecimal(String doubleDecimal) {
        this.doubleDecimal = doubleDecimal;
    }

    //设置Float类型格式
    private void setFloatDecimalFormat(DecimalFormat floatDecimalFormat) {
        this.floatDecimalFormat = floatDecimalFormat;
    }

    //列公式
    private void setColFormula(String[] colFormula) {
        this.colFormula = colFormula;
    }

    //实际执行
    private SXSSFWorkbook execute(List<?> dataList, String titleColumns[], String titleNames[], String sheetName) {

        try {

            //写入excel的表头
            Row titleNamesRow = workbook.getSheet(sheetName).createRow(0);
            //设置样式
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle = (CellStyle) setColor(titleStyle, titleBackColor, (short) 10);

            for (int i = 0; i < titleNames.length; i++) {
                Cell cell = titleNamesRow.createCell(i);
                cell.setCellStyle(titleStyle);
                cell.setCellValue(titleNames[i].toString());
            }
            //通过反射获取数据并写入到excel中
            if (dataList != null && dataList.size() > 0) {
                //设置样式
                CellStyle normalStyle = workbook.createCellStyle();
                CellStyle dateStyle = workbook.createCellStyle();
                DataFormat dateFormat = workbook.createDataFormat();
                dateStyle.setDataFormat(dateFormat.getFormat("yyyy-MM-dd HH:mm:ss"));
                if (titleColumns.length > 0) {
                    for (int rowIndex = 1; rowIndex <= dataList.size(); rowIndex++) {
                        Object obj = dataList.get(rowIndex - 1);     //获得该对象
                        Class aClass = obj.getClass();     //获得该对对象的class实例
                        Row dataRow = workbook.getSheet(sheetName).createRow(rowIndex);
                        for (int columnIndex = 0; columnIndex < titleColumns.length; columnIndex++) {
                            String title = titleColumns[columnIndex].toString().trim();
                            if (!"".equals(title)) {  //字段不为空
                                //使首字母大写
                                String UTitle = Character.toUpperCase(title.charAt(0)) + title.substring(1, title.length()); // 使其首字母大写;
                                String methodName = null;
                                //获取返回类型
								String returnType;
								Object data;
								try {
									Field field = aClass.getDeclaredField(titleColumns[columnIndex]);
									if (StringUtils.equals(field.getType().getName(), "boolean")) {
									    methodName = "is" + UTitle;
									} else {
									    methodName = "get" + UTitle;
									}
									// 设置要执行的方法
									Method method = aClass.getDeclaredMethod(methodName);
									returnType = method.getReturnType().getName();
									data = method.invoke(obj);
								} catch (Exception e) {
								      Map map = (Map) obj;
								    
									  data =map.get(titleColumns[columnIndex]);
									  returnType=data.getClass().getName();
								}
                                Cell cell = dataRow.createCell(columnIndex);
                                if (data != null && !"".equals(data)) {
                                    if ("int".equals(returnType)) {
                                        String strData = data.toString();
                                        cell.setCellStyle(normalStyle);
                                        cell.setCellValue(Integer.parseInt(strData));
                                    } else if ("long".equals(returnType)) {
                                        String strData = data.toString();
                                        cell.setCellStyle(normalStyle);
                                        cell.setCellValue(Long.parseLong(strData));
                                    } else if ("float".equals(returnType)) {
                                        String strData = data.toString();
                                        cell.setCellStyle(normalStyle);
                                        cell.setCellValue(floatDecimalFormat.format(Float.parseFloat(strData)));
                                    } else if ("double".equals(returnType)) {
                                        String strData = data.toString();
                                        cell.setCellStyle(normalStyle);
                                        cell.setCellValue(doubleDecimalFormat.format(Double.parseDouble(strData)));
                                    } else if ("boolean".equals(returnType)) {
                                        boolean booleanData = (boolean) data;
                                        cell.setCellStyle(normalStyle);
                                        cell.setCellValue(booleanData == true ? "是" : "否");
                                    } else if ("java.util.Date".equals(returnType)) {
                                        sheet.setColumnWidth(columnIndex, 5000);
                                        Date date = (Date) data;
                                        cell.setCellStyle(dateStyle);
                                        cell.setCellValue(date);
                                    } else if ("java.math.BigDecimal".equals(returnType)) {
                                        BigDecimal bg = (BigDecimal) data;
                                        double doubleValue = bg.doubleValue();
                                        cell.setCellStyle(normalStyle);
                                        cell.setCellValue(doubleValue);
                                    } else {
                                        String strData = data.toString();
                                        cell.setCellStyle(normalStyle);
                                        cell.setCellValue(strData);
                                    }
                                }
                            } else {   //字段为空 检查该列是否是公式
                                if (colFormula != null) {
                                    String sixBuf = colFormula[columnIndex].replace("@", (rowIndex + 1) + "");
                                    Cell cell = dataRow.createCell(columnIndex);
                                    cell.setCellFormula(sixBuf.toString());
                                }
                            }
                        }
                    }

                }
            }
            return workbook;
        } catch (Exception e) {
            if (logger.isErrorEnabled()) {
                logger.error("poiExport exception", e);
            }
            return new SXSSFWorkbook();
        }

    }

    //带工作簿名入口
    public void writeExcel(List<?> dataList, String titleColumns[], String titleNames[], String fileDir, String sheetName) {
        OutputStream out = null;
        try {
            out = new FileOutputStream(fileDir + ".xlsx");
            //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
            sheet = workbook.createSheet(sheetName);
            //新建文件
            workbook = execute(dataList, titleColumns, titleNames, sheetName);
            workbook.write(out);
        } catch (Exception e) {
            if (logger.isErrorEnabled()) {
                logger.error("writeExcel", e);
            }
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                if (logger.isErrorEnabled()) {
                    logger.error("close", e);
                }
            }
        }
    }

    //不带工作簿名入口生成流
    public HttpServletResponse writeExcel(List<?> dataList, String titleColumns[], String titleNames[], HttpServletResponse response, String excelName) {
    	OutputStream out = null;
        try {
            //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
            String sheetName = "sheet1";
            sheet = workbook.createSheet(sheetName);
            //新建文件
            workbook = execute(dataList, titleColumns, titleNames, sheetName);
            //①指定响应数据的内容类型
            response.setContentType("application/x-msdownload");
            //②指定响应数据的文件名
            response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(excelName + ".xlsx", "utf-8"));
            out = response.getOutputStream();
            workbook.write(out);
        } catch (IOException e) {
            if (logger.isErrorEnabled()) {
                logger.error("sheet", e);
            }
        } finally {
            try {
                out.close();
            } catch (Exception e) {
                if (logger.isErrorEnabled()) {
                    logger.error("close", e);
                }
            }
        }
        return response;
    }

    //不带工作簿名入口生成流
    public void writeExcel(List<?> dataList, String titleColumns[], String titleNames[], OutputStream stream) {
        try {
            //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
            String sheetName = "sheet1";
            sheet = workbook.createSheet(sheetName);
            //新建文件
            workbook = execute(dataList, titleColumns, titleNames, sheetName);
            System.out.println(workbook);
            workbook.write(stream);
        } catch (IOException e) {
            if (logger.isErrorEnabled()) {
                logger.error("sheet", e);
            }
        } finally {
            try {
                out.close();
            } catch (Exception e) {
                if (logger.isErrorEnabled()) {
                    logger.error("close", e);
                }
            }
        }

    }

    //无工作簿名入口
    public void writeExcel(List<?> dataList, String titleColumns[], String titleNames[], String fileDir) {
        OutputStream out = null;
        try {
            out = new FileOutputStream(fileDir + ".xlsx");
            String sheetName = "sheet1";
            sheet = workbook.createSheet(sheetName);
            workbook = execute(dataList, titleColumns, titleNames, sheetName);
            workbook.write(out);
        } catch (Exception e) {
            if (logger.isErrorEnabled()) {
                logger.error("sheet", e);
            }
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                if (logger.isErrorEnabled()) {
                    logger.error("close", e);
                }
            }
        }

    }

    //背景色初始化
    private CellStyle setColor(CellStyle style, String color, short index) {
        if (color != "" && color != null) {
            //转为RGB码
            int r = Integer.parseInt((color.substring(0, 2)), 16);   //转为16进制
            int g = Integer.parseInt((color.substring(2, 4)), 16);
            int b = Integer.parseInt((color.substring(4, 6)), 16);
            //自定义cell颜色
            style.setFillForegroundColor(index);
        }
        return style;
    }

    /**
     * 先转成List<Map<String, String>>再保存到Excel
     * @param valList
     * @param key1
     * @param titleMap
     * @param response
     * @param excelName
     * @return
     */
	public HttpServletResponse writeExcel(List<Map<String, String>> valList, String[] key1, 
			 Map<String,String> titleMap, HttpServletResponse response,String excelName) {
		OutputStream out = null;
        try {
            //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
            String sheetName = "sheet1";
            sheet = workbook.createSheet(sheetName);
            //新建文件
            workbook = execute(valList, key1, titleMap, sheetName);
            //①指定响应数据的内容类型
            response.setContentType("application/x-msdownload");
            //②指定响应数据的文件名
            response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(excelName + ".xlsx", "utf-8"));
            out = response.getOutputStream();
            workbook.write(out);
        } catch (IOException e) {
            if (logger.isErrorEnabled()) {
                logger.error("sheet", e);
            }
        } finally {
            try {
                out.close();
            } catch (Exception e) {
                if (logger.isErrorEnabled()) {
                    logger.error("close", e);
                }
            }
        }
        return response;
	}
	
	//实际执行
    private SXSSFWorkbook execute(List<Map<String, String>> valList, String[] key, Map<String,String> titleMap, String sheetName) {
        try {
            //写入excel的表头
            Row titleNamesRow = workbook.getSheet(sheetName).createRow(0);
            //设置样式
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle = (CellStyle) setColor(titleStyle, titleBackColor, (short) 10);

            for (int i = 0; i < key.length; i++) {
                Cell cell = titleNamesRow.createCell(i);
                cell.setCellStyle(titleStyle);
                cell.setCellValue(titleMap.get(key[i]));
            }
            //通过反射获取数据并写入到excel中
            if (valList != null && valList.size() > 0) {
                //设置样式
                CellStyle normalStyle = workbook.createCellStyle();
                if (key.length > 0) {
                    for (int rowIndex = 1; rowIndex <= valList.size(); rowIndex++) {
                    	Map<String,String> valMap = valList.get(rowIndex-1);
                        Row dataRow = workbook.getSheet(sheetName).createRow(rowIndex);
                        for (int columnIndex = 0; columnIndex < key.length; columnIndex++) {
                            String title = titleMap.get(key[columnIndex]);
                            if (!"".equals(title)) {  //字段不为空
                            	Cell cell = dataRow.createCell(columnIndex);
                            	cell.setCellStyle(normalStyle);
                            	cell.setCellValue(valMap.get(key[columnIndex]));
                            } else {   //字段为空 检查该列是否是公式
                                if (colFormula != null) {
                                    String sixBuf = colFormula[columnIndex].replace("@", (rowIndex + 1) + "");
                                    Cell cell = dataRow.createCell(columnIndex);
                                    cell.setCellFormula(sixBuf.toString());
                                }
                            }
                        }
                    }

                }
            }
            return workbook;
        } catch (Exception e) {
            if (logger.isErrorEnabled()) {
                logger.error("poiExport exception", e);
            }
            return new SXSSFWorkbook();
        }

    }
}
